

'''
模型类DictDB，用次模型类的对象可以直接操作数据库
'''

import pymysql
import re
import hashlib

import hashlib
def password_encrypt(passwd):
    '''加密密码，生成密码摘要'''
    hash = hashlib.sha256(b'weimz')
    hash.update(passwd.encode())
    return hash.hexdigest()



class DictDB:
    def __init__(self):
        self.conn = pymysql.connect(
            host = '127.0.0.1',
            port = 3306,
            user = 'root',
            password = '123456',
            database = 'dict_db',
            charset='utf8'
        )
        self.cur = None  # self.cur 用来绑定游标

    def __del__(self):
        self.conn.close() # 关闭数据库连接

    def create_cursor(self):
        '创建游标'
        if self.cur is None:
            self.cur = self.conn.cursor()

    def close_cursor(self):
        '关闭游标'
        if self.cur:
            self.cur.close()
        self.cur = None

    def init_dict_data(self):
        '''将dict.txt 的内容导入到dict_db.words表中'''
        datas = []
        try:
            with open('dict.txt') as fr:
                for aline in fr:
                    aline = aline.strip()  # 去掉空白字符串
                    item_list = re.findall(
                        r'(\w+)\s+(.*)', aline)
                    if item_list:
                        datas.append(item_list[0])
        except Exception as err:
            print('初始化词典数据失败！', err)
        sql = 'insert into words(word, trans) values(%s,%s);'
        try:
            # 将 datas 中的内容插入到 dict_db.words 表中
            self.cur.executemany(sql, datas)
            self.conn.commit()
            print("字典数据初始化成功")
        except Exception as err:
            self.conn.rollback()
            print('init dict_table 失败', err)

    def add_user(self, username, password):
        '''
        为注册调用的方法
        :param username: 用户名
        :param password:密码
        :return: 成功返回True, 失败返回False
        '''
        password = password_encrypt(password)
        sql = 'insert into users (username, password) values (%s, %s);'
        try:
            self.cur.execute(sql, (username, password))
            self.conn.commit()
            return True
        except:
            self.conn.rollback()
            return False

    def check_user(self, username, password):
        '''
        为登录功能使用
        :param username: 用户名
        :param password: 密码
        :return:  当前用户名和密码与数据库中完全一致，
                 返回True, 否则返回False
        '''
        password = password_encrypt(password)
        sql = 'select * from users where username=%s and password=%s;'
        try:
            self.cur.execute(sql, (username, password))
            result = self.cur.fetchone()
            if result:
                return True
            else:
                return False
        except:
            return False

    def query_word(self, word):
        '''
        查询单词， words 表
        :param word: 要查询的词
        :return:  如果word 在 数据库内，
        返回单词解释的字符串, 如果不存在返回None
        '''
        sql = 'select trans from words where word=%s;'
        self.cur.execute(sql, [word])
        item = self.cur.fetchone()
        if item:
            return item[0]
        else:
            return None

    def insert_hist(self, name, word):
        '''
        记录查询历史, 将name,word 插入到hists
        数据表
        :param name:  用户名
        :param word:  查过的单词
        :return:    None
        '''
        try:
            sql = 'insert into hists (username, word) values(%s,%s);'
            self.cur.execute(sql, [name, word])
            self.conn.commit()
            print("insert ok")
        except:
            self.conn.rollback()
            print('insert failed')

    def get_hist(self, name):
        '''
        根据name 在 hists 表中，查找 历史记录
        中的最后10条
        :param name:
        :return:  格式:'name1,word1,2010-1-1 12:00;name1, world2, ..'
        '''
        sql = 'select username, word, time from hists where username=%s order by time desc limit 10;'
        self.cur.execute(sql, name)
        hist_list = []
        for item in self.cur.fetchall():
            s = '%s,%s,%s' % item
            hist_list.append(s)
        return ';'.join(hist_list)


if __name__ == '__main__':
    db = DictDB()
    db.create_cursor()
    db.init_dict_data()
    # db.add_user('laowei', '123456')
    # print(db.check_user('laowei', '123456'))
    # print(db.query_word('world'))
    # db.insert_hist('laowei', 'hello')
    # db.insert_hist('laowei', 'word')
    # print(db.get_hist('laowei'))